# 本模块功能：连接用户和管理员数据库
import sqlite3
from sqlite3 import Error


class DatabaseManager:
    def __init__(self, db_name):
        """初始化数据库连接，db_name为数据库文件名"""
        self.db_name = db_name
        self.conn = None
        self.cursor = None
        self.create_connection()

    def create_connection(self):
        """创建数据库连接"""
        try:
            self.conn = sqlite3.connect(self.db_name)
            self.cursor = self.conn.cursor()
            print(f"Connected to {self.db_name} database.")
        except Error as e:
            print(f"The error '{e}' occurred while connecting to the database.")

    def initialize_db(self):
        """初始化数据库，创建所需的表"""
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL UNIQUE,
                password TEXT NOT NULL,
                email TEXT NOT NULL UNIQUE
            )
        ''')
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS admins (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL UNIQUE,
                password TEXT NOT NULL,
                email TEXT NOT NULL UNIQUE
            )
        ''')
        # 可以继续添加其他表的创建语句
        self.conn.commit()

    def execute_sql(self, sql, params=None):
        """执行任意SQL语句"""
        try:
            if params:
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql)
            self.conn.commit()
        except Error as e:
            print(f"The error '{e}' occurred while executing SQL.")

    def insert_user(self, username, password, email):
        """插入新用户"""
        sql = "INSERT INTO users (username, password, email) VALUES (?, ?, ?)"
        self.execute_sql(sql, (username, password, email))

    def insert_admin(self, username, password, email):
        """插入新管理员"""
        sql = "INSERT INTO admins (username, password, email) VALUES (?, ?, ?)"
        self.execute_sql(sql, (username, password, email))

    # 可以继续添加其他增删改查的方法

    def query_data(self, sql, params=None):
        """查询数据"""
        try:
            if params:
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql)
            return self.cursor.fetchall()
        except Error as e:
            print(f"The error '{e}' occurred while querying data.")

    def close_connection(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            print("Database connection is closed.")


# 使用示例
if __name__ == '__main__':
    # 连接用户数据库
    db_manager = DatabaseManager('database/users.db')
    db_manager.initialize_db()  # 如果需要初始化数据库
    db_manager.insert_user('newuser', 'securepassword', 'user@example.com')
    users = db_manager.query_data("SELECT * FROM users")

    print(users)
    db_manager.close_connection()

    # 连接管理员数据库
    admin_manager = DatabaseManager('database/admins.db')
    # ... 进行数据库操作 ...
